package org.yoao.crm.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.yoao.crm.DBManager.DBManager;
import org.yoao.crm.bean.CustomerAttributes;

/**
 * @author Weishion.Yi
 * @description
 * @date 2010-11-20 下午03:23:23
 */
public class CustomerAttributesDAO extends DBManager {

	public CustomerAttributes findById(String id) throws Exception {
		String sql = "select customer.customerName, CustomerAttributes.* from CustomerAttributes "
				+ "left join customer on customer.customerId = CustomerAttributes.customerId "
				+ "where CustomerAttributes.id=" + id;
		return findSingle(querySingleRow(sql));
	}

	public CustomerAttributes findByCustomerIdAndState(String customerId,
			String state) throws Exception {
		String sql = "select * from CustomerAttributes where customerId='"
				+ customerId + "' and state='" + state + "'";
		return findSingle(querySingleRow(sql));
	}

	public List findAll(String customerId, Map<String, String> map)
			throws Exception {
		String sql = "select customerAttributes.id, customerAttributes.customerId, customerType.typeName as state, "
				+ "customerAttributes.signature, customerAttributes.signTime, customerAttributes.amount, "
				+ "customerAttributes.payTime from customerAttributes "
				+ "left join customerType on customerType.typeCode=customerAttributes.state "
				+ "where customerId='" + customerId + "'";
		String where = "";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				if (key.equals("customerAttributes.state"))
					where = where + " and " + key + "=" + setNull(value);
				else if (key.indexOf(">= ") > 0)
					where = where + " and " + key + value;
				else
					where = where + " and " + key + "=" + value;
			}
		}
		if (where != null && !where.trim().equals(""))
			sql = sql + where;
		return find(sql);
	}

	private List find(String sql) throws Exception {
		List list = queryMultiColumn(sql);
		List result = null;
		if (list != null && !list.isEmpty()) {
			result = new ArrayList();
			for (int i = 0; i < list.size(); i++) {
				Map map = (Map) list.get(i);
				CustomerAttributes customerAttributes = findSingle(map);
				if (customerAttributes == null)
					customerAttributes = new CustomerAttributes();
				result.add(customerAttributes);
			}
		}
		return result;
	}

	private CustomerAttributes findSingle(Map data) throws Exception {
		CustomerAttributes customerAttributes = null;
		if (data != null && !data.isEmpty()) {
			customerAttributes = new CustomerAttributes();
			customerAttributes.setId(Integer
					.parseInt(data.get("ID").toString()));
			customerAttributes.setCustomerId((String) data.get("CUSTOMERID"));
			customerAttributes.setCustomerName((String) data
					.get("CUSTOMERNAME"));
			customerAttributes.setState((String) data.get("STATE"));
			customerAttributes.setSignature(Integer.parseInt(data.get(
					"SIGNATURE").toString()));
			customerAttributes.setSignTime((String) data.get("SIGNTIME"));
			customerAttributes.setAmount(Float
					.parseFloat(data.get("AMOUNT") != null ? data.get("AMOUNT")
							.toString() : "0"));
			customerAttributes.setPayTime((String) data.get("PAYTIME"));
		}
		return customerAttributes;
	}

	public int insert(CustomerAttributes customerAttributes) throws Exception {
		String sql = "insert into CustomerAttributes(customerId, state, signature, signTime, amount, payTime)values("
				+ setNull(customerAttributes.getCustomerId())
				+ ", "
				+ setNull(customerAttributes.getState())
				+ ", "
				+ customerAttributes.getSignature()
				+ ", "
				+ setNull(customerAttributes.getSignTime())
				+ ", "
				+ customerAttributes.getAmount()
				+ ", "
				+ setNull(customerAttributes.getPayTime()) + ")";
		int result = -1;
		if (update(sql) > -1) {
			sql = "select id from CustomerAttributes where customerId='"
					+ customerAttributes.getCustomerId() + "' and state='"
					+ customerAttributes.getState() + "'";
			String r = querySingleValue(sql);
			if (r != null && !r.trim().equals("")) {
				result = Integer.parseInt(r);
			}
		}
		return result;
	}

	public int modify(CustomerAttributes customerAttributes) throws Exception {
		String sql = "update CustomerAttributes set state="
				+ setNull(customerAttributes.getState()) + ", signature="
				+ customerAttributes.getSignature() + ", signTime="
				+ setNull(customerAttributes.getSignTime()) + ", amount="
				+ customerAttributes.getAmount() + ", payTime="
				+ setNull(customerAttributes.getPayTime()) + " where id="
				+ customerAttributes.getId();
		return update(sql);
	}

	public int modifyByCustomerIdAndState(CustomerAttributes customerAttributes)
			throws Exception {
		String sql = "update CustomerAttributes set signature="
				+ customerAttributes.getSignature() + ", signTime='"
				+ customerAttributes.getSignTime() + "', amount="
				+ customerAttributes.getAmount() + ", payTime='"
				+ customerAttributes.getPayTime() + "' where customerId='"
				+ customerAttributes.getCustomerId() + "' and state='"
				+ customerAttributes.getState() + "'";
		return update(sql);
	}

	public int sign(String id, String value, String signTime,
			String customerType, String customerId) throws Exception {
		if ("0".equals(value))
			signTime = null;
		String sql = "update CustomerAttributes set signature=" + value
				+ ", signTime=" + setNull(signTime);
		if (id != null) {
			if (id.indexOf(",") > 0)
				sql = sql + " where id in(" + id + ")";
			else
				sql = sql + " where id=" + id;
		}
		if (customerId != null) {
			if (customerId.indexOf(",") > 0) {
				customerId = "'" + customerId.replaceAll(",", "','") + "'";
				sql = sql + " where customerId in(" + customerId + ")";
			} else
				sql = sql + " where customerId='" + customerId + "'";
		}
		if (customerType != null) {
			sql = sql + " and state='" + customerType + "'";
		}
		return update(sql);
	}

	public int delete(String id) throws Exception {
		String sql = null;
		if (id.indexOf(",") > 0)
			sql = "delete from customerAttributes where id in(" + id + ")";
		else
			sql = "delete from customerAttributes where id=" + id;
		return update(sql);
	}

	public int deleteByCustomerId(String customerId) throws Exception {
		String sql = null;
		if (customerId.indexOf(",") > 0) {
			customerId = "'" + customerId.replaceAll(",", "','") + "'";
			sql = "delete from customerAttributes where customerId in("
					+ customerId + ")";
		} else
			sql = "delete from customerAttributes where customerId='"
					+ customerId + "'";
		return update(sql);
	}
}